package db_layer;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * This is a class holding useful functions that can be used throughout the database layer.
 * All of these functions are declared as static, to avoid being forced to create instances of DBUtilities class.
 * These functions consist mostly of database functions used in almost all database classes (to avoid repetition).
 * This class exists solely for the convenience of the programmer and to decrease amount of work and code repetition.
 * @author Rafal Markiewicz
 * 
 */
public class DBUtilities
{

	public DBUtilities()
	{
		// TODO Auto-generated constructor stub
	}

	/**
	 * This function returns a maximum value (highest value) of desired variable from desired table in the database.
	 * It requires a working connection to a database a name of the table to be searched and a name of the searched variable.
	 * In case of failure this function returns -1.
	 */
	public static int get_max(Connection con, String table_name, String variable_name)
	{
		ResultSet results;
		int max = -1;
		String query = String.format("SELECT max(%s) FROM %s", variable_name, table_name);
		try
		{ 
		   Statement stmt = con.createStatement();
		   results = stmt.executeQuery(query);
		   if( results.next() )
		   {
			 max = results.getInt(1);
		   }
		   else
		   {
			   max = 1;
		   }
		}
		catch(Exception e)
		{
		    System.out.println("Query exception: Error while reading maximum " + variable_name + " from " + table_name);
		    System.out.println(e);
		}
		return max;
	}
	
	
	/**
	 * A convenience function for creating a prepared insert statement (for a single row)
	 * It requires a working connection to a database, name of a table and names of all the variables to be inserted in desired order
	 * (given as string, with each variable separated by comma).
	 */
	 public static PreparedStatement make_insert_statement(Connection con, String table, String var_names)
	{
		return make_multiple_insert_statement(con, table, var_names, 1);
	}
    
	 /**
	  * A convenience function for creating a prepared insert statement (for multiple rows)
	  * It requires a working connection to a database, name of a table, names of all the variables to be inserted in desired order
	  * (given as string, with each variable separated by comma) and a number of rows to be inserted.
	  * * IMPORTANT!
	  * PreparedStatement returned by this function needs to be finished. 
	  * Values of all of the variables to be inserted need to be set before the statement can be executed.
	  */
    public static PreparedStatement make_multiple_insert_statement(Connection con, String table, String var_names, int times)
    {
    	String quest_marks = "";
		int var_num = var_names.split(",").length;
		for(int i = 0; i < var_num; i++)
		{
			if(i==0)
			{
				quest_marks += "?";
			}
			else
			{
				quest_marks += ", ?";
			}
		}
		try
		{
			String query = String.format("INSERT INTO %s(%s) VALUES(%s)", table, var_names, quest_marks);
			for (int i = 0; i < times-1; i++)
			{
				query += String.format(", (%s)", quest_marks);
			}
			 return con.prepareStatement(query);
		}
		catch(SQLException se)
		{
			System.out.println("Error while creating insert statement: " + se);
			return null;
		}
    }
    
    /**
	  * A convenience function for creating a prepared update statement.
	  * It requires a working connection to a database, name of a table, names of all the variables to be updated in desired order 
	  * (given as string, with each variable separated by comma) and a name of a variable by which the desired row is to be found (WHERE statement variable).
	  * IMPORTANT!
	  * PreparedStatement returned by this function needs to be finished. 
	  * Values of all of the variables to be updated and conditional variable need to be set before the statement can be executed.
	  */
    public static PreparedStatement make_update_statement(Connection con, String table, String var_names, String where_var)
    {
    	String query = String.format("UPDATE %s SET ", table);
    	String[] vars = var_names.split(",");
    	for (int i = 0; i < vars.length; i++)
    	{
    		if (i==0)
    		{
    			query += vars[i] + " = ?";
    		}
    		else
    		{
    			query += ", " + vars[i] + " = ?";
    		}
    	}
    	if(where_var.length() > 0)
    	{
    		query += " WHERE " + where_var + " = ?";
    	}
    	try
    	{
	    	PreparedStatement ps = con.prepareStatement(query);
	    	System.out.println(query);
	    	return ps;
    	}
    	catch(SQLException se)
    	{
    		System.out.println("Error while creating update statement: " + se);
    		return null;
    	}
    }
    
    
    public static  PreparedStatement make_delete_statement(Connection con, String table_name, String var_name)
    {

    	
    	String query = String.format("DELETE FROM %s WHERE %s = ?", table_name, var_name);
    	try
    	{
    		PreparedStatement ps = con.prepareStatement(query);
    		System.out.println(query);
    		return ps;
    	}
    	catch(SQLException e)
    	{
    		System.out.println("Error while creating a delete statement: " + e);
    		return null;
    	}
    }
    
    /**
     * A convenience function for creating prepared search queries.
     * It requires a working database connection, a proper SQL query (with or without a WHERE statement),
     * name of the variable to search by (in the WHERE statement) and either variable value given as String or as an integer.
     * If the name of the variable is an empty String, the where clause is not added to the statement.
     * If the variable name is given but the String representation of variable is an empty String, than variable the variable is assumed
     * to be an integer and a query is created with the integer representation. Otherwise it is created with the given String representation of the variable.
     * String representation always takes precedence over integer representation.
     * Statement returned by this method is ready to be executed.
     */
    public static PreparedStatement prepare_statement(Connection con, String query, String var_name, String s_var, int i_var)
    {
		PreparedStatement ps = null;
		try
		{
			
			if(var_name.length() > 0)
			{
				String where_clause;
				if(query.contains("WHERE"))
				{
					where_clause = String.format(" AND %s = ?", var_name);
				}
				else
				{
					where_clause = String.format(" WHERE %s = ?", var_name);
				}
				
				query += where_clause;
				ps = con.prepareStatement(query);
				
				if(s_var.length() > 0)
				{
					ps.setString(1, s_var);
				}
				else
				{
					ps.setInt(1, i_var);
				}
			}
			else
			{
				ps = con.prepareStatement(query);
			}
		}
		catch (SQLException e)
		{
			System.out.println("Error while creating a statement: " + e);
		}
		System.out.println(query);
		return ps;
	}
    
	/**
	 * A convenience function for creating prepared search queries.
     * It requires a working database connection, a proper SQL query (with or without a WHERE statement),
     * name of the variable to search by (in the WHERE statement) and variable value provided as a String.
     * Statement returned by this method is ready to be executed.
	 */
    public static PreparedStatement prepare_statement(Connection con, String query, String var_name, String var)
	{
		PreparedStatement ps = null;
		String where_clause;
		if(query.contains("WHERE"))
		{
			where_clause = String.format(" AND %s = ?", var_name);
		}
		else
		{
			where_clause = String.format(" WHERE %s = ?", var_name);
		}
		try
		{
			ps = con.prepareStatement(query + where_clause);
			ps.setString(1, var);
		}
		catch (SQLException e)
		{
			System.out.println("Error while creating a statement: " + e);
		}
		return ps;
	}
    
    /**
	 * A convenience function for creating prepared search queries.
     * It requires a working database connection, a proper SQL query (with or without a WHERE statement),
     * name of the variable to search by (in the WHERE statement) and variable value provided as a integer.
     * Statement returned by this method is ready to be executed.
	 */
    public static PreparedStatement prepare_statement(Connection con, String query, String var_name,  int var)
    {
    	PreparedStatement ps = null;
    	String where_clause;
		if(query.contains("WHERE"))
		{
			where_clause = String.format(" AND %s = ?", var_name);
		}
		else
		{
			where_clause = String.format(" WHERE %s = ?", var_name);
		}
		try
		{
			ps = con.prepareStatement(query + where_clause);
			ps.setInt(1, var);
		}
		catch (SQLException e)
		{
			System.out.println("Error while creating a statement: " + e);
		}
		return ps;
    }
    
    /**
	 * A convenience function for creating prepared search queries.
     * It requires a working database connection, a proper SQL query (with or without a WHERE statement).
     * Statement returned by this method is ready to be executed.
	 */
    public static PreparedStatement prepare_statement(Connection con, String query)
    {
    	PreparedStatement ps = null;
    	try
    	{
    		ps = con.prepareStatement(query);
    	}
    	catch (SQLException e)
		{
			System.out.println("Error while creating a statement: " + e);
		}
		return ps;
    }
    
    /**
     * A function for creating a proper SELECT SQL query.
     * It takes as parameters names of variables to be selected (given as a single string with names of variables separated by commas),
     * names of the tables from which to select (presented in the same way as variable names)
     * and conditions on which rows are to be selected (also presented in the same way as variable names: e.g "id = 5, name = 'John'")
     * This function assumes that all conditions will have to be meet to qualify the row to be selected ( they will be separated by ANDs)
     */
    public static String make_query(String var_names, String tables, String wheres)
    { 
    	String[] where_vars = wheres.split("'");
    	String sql_where = "";
    	for (String where : where_vars)
    	{
    		if (sql_where.length() == 0)
    		{
    			sql_where += where;
    		}
    		else
    		{
    			sql_where += " AND " + where;
    		}
    	}
    	return String.format("SELECT %s FROM %s WHERE %s", var_names, tables, sql_where);
    }
    
    /**
     * A function for creating a proper SELECT SQL query.
     * It takes as parameters names of variables to be selected (given as a single string with names of variables separated by commas),
     * names of the tables from which to select (presented in the same way as variable names)
     */
    public static String make_query(String var_names, String tables)
    {
    	return String.format("SELECT %s FROM %s", var_names, tables);
    }
    
    
}
